package com.kwum.uctoutiao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class MySQLUtils {

    public static void main(String[] args) throws Exception{
        
//        //获得数据库的连接
//        Connection conn = getMySQLConnection("articlespirder");
//        //通过数据库的连接操作数据库，实现增删改查
//        Statement stmt = conn.createStatement();
//        ResultSet rs = stmt.executeQuery("select username, password from account");//选择import java.sql.ResultSet;
//        while(rs.next()){//如果对象中有数据，就会循环打印出来
//            System.out.println(rs.getString("username")+","+rs.getString("password"));
//        }
        
    	JSONArray array = new JSONArray();
    	JSONObject json = new JSONObject();
    	json.put("name", "要闻");
    	json.put("value", "news_video_top");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "娱乐");
    	json.put("value", "news_news_ent");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "体育");
    	json.put("value", "news_news_sports");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "军事");
    	json.put("value", "news_news_mil");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "NBA");
    	json.put("value", "news_news_nba");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "国际");
    	json.put("value", "news_news_world");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "科技");
    	json.put("value", "news_news_tech");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "财经");
    	json.put("value", "news_news_finance");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "汽车");
    	json.put("value", "news_news_car");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "时尚");
    	json.put("value", "news_news_lad");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "电影");
    	json.put("value", "news_news_movie");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "独家");
    	json.put("value", "news_news_orignal");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "游戏");
    	json.put("value", "news_news_game");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "房产");
    	json.put("value", "news_news_house");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "政务");
    	json.put("value", "news_news_msh");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "图片");
    	json.put("value", "news_video_main");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "育儿");
    	json.put("value", "news_news_baby");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "文化");
    	json.put("value", "news_news_cul");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "佛学");
    	json.put("value", "news_news_fx");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "股票");
    	json.put("value", "news_news_istock");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "理财");
    	json.put("value", "news_news_lic");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "家居");
    	json.put("value", "news_news_jiaju");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "电竞");
    	json.put("value", "news_news_esport");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "数码");
    	json.put("value", "news_news_digi");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "星座");
    	json.put("value", "news_news_astro");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "教育");
    	json.put("value", "news_news_edu");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "美容");
    	json.put("value", "news_news_meirong");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "旅游");
    	json.put("value", "news_news_visit");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "电视剧");
    	json.put("value", "news_news_tv");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "搏击");
    	json.put("value", "news_news_strike");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "健康");
    	json.put("value", "news_news_health");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "生活");
    	json.put("value", "news_news_lifes");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "韩流");
    	json.put("value", "news_news_hanliu");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "综艺");
    	json.put("value", "news_news_zongyi");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "美食");
    	json.put("value", "news_news_food");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "宠物");
    	json.put("value", "news_news_pet");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "历史");
    	json.put("value", "news_news_history");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "音乐");
    	json.put("value", "news_news_music");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "传媒");
    	json.put("value", "news_news_media");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "科普s");
    	json.put("value", "news_news_kepu");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "足球");
    	json.put("value", "news_news_football");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "CBA");
    	json.put("value", "news_news_cba");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "必读");
    	json.put("value", "news_news_twentyf");
    	array.add(json);
    	json = new JSONObject();
    	json.put("name", "校园");
    	json.put("value", "news_news_schoolyard");
    	array.add(json);
    	for(int i = 0; i < array.size(); i++) {
    		insertCategory(array.getJSONObject(i).getString("name"), array.getJSONObject(i).getString("value"), 2);
    	}
    }
    
    /**
     * 获取数据库连接
     * @param databaseName 数据库名
     * @return
     * @throws Exception
     * @author Kwum
     */
    public static Connection getMySQLConnection(String databaseName) throws Exception{
        //声明Connection对象
        Connection con;
        //驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        //URL指向要访问的数据库名mydata
        String url = "jdbc:mysql://localhost:3306/" + databaseName + "?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false";
        //MySQL配置时的用户名
        String user = "root";
        //MySQL配置时的密码
        String password = "Kwum123";
        //加载驱动程序
        Class.forName(driver);
        //1.getConnection()方法，连接MySQL数据库！！
        con = DriverManager.getConnection(url,user,password);
        if(!con.isClosed()){
//            System.out.println("The database connection is complete!");
        }
        return con;
    }
    
    /**
     * 插入UC头条数据
     * @param uctoutiao UC头条
     * @throws Exception
     * @author Kwum
     */
    public static void insertUCToutiaoList(UCToutiao uctoutiao) throws Exception{
        //获得数据库的连接
        Connection conn = getMySQLConnection("articlespirder");
        
        String sql="insert ignore into uc_toutiao_list (uctoutiao_id, title, publish_time, url, author, ori_author, ori_url, category,"
        		+ "comment_count) "
        		+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?)";
        //预编译sql语句
        PreparedStatement psmt = conn.prepareStatement(sql);
        //先对应SQL语句，给SQL语句传递参数
        psmt.setString(1, uctoutiao.getUcToutiaoId());
        psmt.setString(2, uctoutiao.getTitle());
        psmt.setString(3, uctoutiao.getPublishTime());
        psmt.setString(4, uctoutiao.getUrl());
        psmt.setString(5, uctoutiao.getAuthor());
        psmt.setString(6, uctoutiao.getOriAuthor());
        psmt.setString(7, uctoutiao.getOriUrl());
        psmt.setString(8, uctoutiao.getCategory());
        psmt.setInt(9, uctoutiao.getCommentCount());
        //执行SQL语句
        psmt.execute();
        conn.close();
    }
    
    /**
     * 插入腾讯新闻数据
     * @param tencentNew 腾讯新闻
     * @throws Exception
     * @author Kwum
     */
    public static void insertTencentNewList(TencentNew tencentNew) throws Exception{
        //获得数据库的连接
        Connection conn = getMySQLConnection("articlespirder");
        
        String sql="insert ignore into tencent_new_list (tencent_new_id, title, url, author, publish_time, comment_count, category) "
        		+ "values(?, ?, ?, ?, ?, ?, ?)";
        //预编译sql语句
        PreparedStatement psmt = conn.prepareStatement(sql);
        //先对应SQL语句，给SQL语句传递参数
        psmt.setString(1, tencentNew.getTencentNewId());
        psmt.setString(2, tencentNew.getTitle());
        psmt.setString(3, tencentNew.getUrl());
        psmt.setString(4, tencentNew.getAuthor());
        psmt.setString(5, tencentNew.getPublishTime());
        psmt.setInt(6, tencentNew.getCommentCount());
        psmt.setString(7, tencentNew.getCategory());
        //执行SQL语句
        psmt.execute();
        conn.close();
    }
    
    public static void insertCategory(String name, String value, Integer sourceId) throws Exception{
        //获得数据库的连接
        Connection conn = getMySQLConnection("articlespirder");
        
        String sql="insert ignore into category (name, value, source) "
        		+ "values(?, ?, ?)";
        //预编译sql语句
        PreparedStatement psmt = conn.prepareStatement(sql);
        //先对应SQL语句，给SQL语句传递参数
        psmt.setString(1, name);
        psmt.setString(2, value);
        psmt.setInt(3, sourceId);
        //执行SQL语句
        psmt.execute();
        conn.close();
    }
}
